using System;
using System.Data.SqlClient;
using Budgeteer.Services.Domain;

namespace Budgeteer.Services.DataProviders
{
    public class ChargeDataProvider : BaseDataProvider
    {
        public ChargeDataProvider(string connectionString)
            : base(connectionString)
        {}

        public ChargeDto GetChargeData(int chargeId)
        {
            using(SqlConnection conn = CreateConnection())
            {
                string sql = @"
                 SELECT CHG.Id, CHG.Time, CHG.Name, CHG.Amount, CAT.Name AS Category, ACCT.Name AS Account
                 FROM Charge CHG
	                INNER JOIN Category CAT ON (CHG.CategoryId = CAT.Id)
	                INNER JOIN Account ACCT ON (CHG.AccountId = ACCT.Id)
                 WHERE CHG.Id = @ChargeId
                ";

                using(SqlCommand command = CreateCommand(sql, conn))
                {
                    SetParameter(command, "@ChargeId", chargeId);

                    using(SqlDataReader reader = command.ExecuteReader())
                    {
                        if(reader == null || !reader.Read())
                            return null;

                        return new ChargeDto
                        {
                            Id = Convert.ToInt32(reader["Id"]),
                            Time = Convert.ToDateTime(reader["Time"]),
                            Name = Convert.ToString(reader["Name"]),
                            Amount = Convert.ToDecimal(reader["Amount"]),
                            Category = Convert.ToString(reader["Category"]),
                            Account = Convert.ToString(reader["Account"])
                        };
                    }
                }
            }
        }

        public virtual void InsertTransactionData(ChargeDto charge)
        {
            using(SqlConnection conn = CreateConnection())
            {
                string sql = @"
                    INSERT INTO [Charge] 
                        (Time, Name, Amount, CategoryId, AccountId) 
                    VALUES 
                        (@Time, @Name, @Amount, 
                         (SELECT Id FROM Category CAT WHERE CAT.Name = @Category), 
                         (SELECT Id FROM Account ACCT WHERE ACCT.Name = @Account)
                        ); 

                    SELECT SCOPE_IDENTITY();
                ";

                using(SqlCommand insertTransaction = CreateCommand(sql,conn))
                {
                    SetParameter(insertTransaction, "@Time", charge.Time);
                    SetParameter(insertTransaction, "@Name", charge.Name);
                    SetParameter(insertTransaction, "@Amount", charge.Amount);
                    SetParameter(insertTransaction, "@Category", charge.Category);
                    SetParameter(insertTransaction, "@Account", charge.Account);

                    try
                    {
                        charge.Id = Convert.ToInt32(insertTransaction.ExecuteScalar());
                    }
                    catch (SqlException ex)
                    {
                        switch (ex.Number)
                        {
                            default:
                                throw;
                        }
                    }
                }
            }
        }
    }
}